﻿-- Function: pl_function.guardar_dresumn(character varying, character varying, character varying)

-- DROP FUNCTION pl_function.guardar_dresumn(character varying, character varying, character varying);

CREATE OR REPLACE FUNCTION pl_function.guardar_dresumn(p_mhresum character varying, p_vhostnm character varying, p_vusernm character varying)
  RETURNS integer AS
$BODY$declare
	p_idsigma numeric(18, 0) := 0;
	p_ntotpre int;
begin

	delete from registro.dresumn where mhresum=p_mhresum;
	select coalesce(max(idsigma)::numeric(18, 0), 0) into p_idsigma from registro.dresumn; 

	select count(1) into p_ntotpre from registro.dpredio where mhresum=p_mhresum and nestado=1;

	insert into registro.dresumn(
		  idsigma, mhresum, cperiod, nautova
		, nbaseim, nimpanu, nimptri, ntotpre
		, nestado, vhostnm, vusernm, ddatetm
		, ncosemi
	)
	select
		  idsigma, mhresum, cperiod, nautova
		, nbaseim, nimpanu, trunc(nimpanu / 4, 2) nimptri, ntotpre
		, 1 nestado, p_vhostnm vhostnm, p_vusernm vusernm, now() ddatetm
		, ncosemi
	from (
		select 
			lpad(((row_number() over(order by t.cperiod)) + p_idsigma)::varchar, 10, '0') idsigma
			, p_mhresum mhresum, t.cperiod, t.nvalpre nautova
			, t.nvalafe nbaseim, p_ntotpre ntotpre, t.ncosemi + (t.ncosadi * (p_ntotpre  - 1)) ncosemi
			, round(case 
			when t.nvalafe >= 0 and t.nvalafe <= (t.nvaluit * 15) then
				( case when (t.nvalafe * 0.2 / 100) < t.nimpmin then t.nimpmin else (t.nvalafe * 0.2 / 100) end )
			when t.nvalafe > (t.nvaluit * 15) and t.nvalafe <= (t.nvaluit * 60) then
				((t.nvalafe - t.nvaluit * 15) * 0.6 /100) + (t.nvaluit * 15 * 0.2 /100)
			when t.nvalafe > (t.nvaluit * 60) then
				((t.nvalafe - t.nvaluit * 60) * 1 /100) + ( 
					(t.nvaluit * 15 * 0.2 / 100) +
					((t.nvaluit * 60 * 0.6 /100) - (t.nvaluit * 15 * 0.6 / 100))
				)	
			else t.nimpmin end, 2) nimpanu
		from (
			select b.cperiod, sum(b.nvalpre) nvalpre, sum(b.nvalafe) nvalafe, c.nvaluit, c.nimpmin, c.ncosemi, c.ncosadi
			from registro.dpredio a 
				inner join registro.vpredio b on a.idsigma=b.dpredio
				inner join public.cvaluit c on b.cperiod=c.cperiod
			where a.mhresum=p_mhresum
				and b.nestado=1
				and c.ctiptri='02.01'
				and c.cperiod::int>to_char(a.dfecadq, 'yyyy')::int
			group by b.cperiod, c.nvaluit, c.nimpmin, c.ncosemi, c.ncosadi
		) t
	) x;
return 1;
exception
when sqlstate '57014' then
	raise notice 'Error';
	return 0;
end
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION pl_function.guardar_dresumn(character varying, character varying, character varying)
  OWNER TO postgres;
COMMENT ON FUNCTION pl_function.guardar_dresumn(character varying, character varying, character varying) IS '  ';
